package beanstao.util.store.dal;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import net.sf.json.JSONArray;

import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import beanstao.util.enums.DB;
import beanstao.util.enums.DataType;
import beanstao.util.enums.operator.Comparison;
import beanstao.util.id.Id;
import beanstao.util.log.LogHelper;
import beanstao.util.store.dal.interfaces.IDomainSelect;
import beanstao.util.store.dal.interfaces.IDomainUpdate;
import beanstao.util.store.database.Columns;
import beanstao.util.store.database.DbSetting;
import beanstao.util.store.database.Field;
import beanstao.util.store.database.Fields;
import beanstao.util.store.database.Where;
import beanstao.util.store.database.impl.OracleDbCreator;
import beanstao.util.store.database.interfaces.IDbCreator;
import beanstao.util.store.database.interfaces.IDbHelper;
import beanstao.util.store.database.interfaces.IDbVerify;
import beanstao.util.store.database.interfaces.ISqls;
import beanstao.util.store.domainobject.Domain;
import beanstao.util.store.domainobject.DomainList;

import com.google.common.collect.Lists;

/**
 * 这是一个针对DomainObject的Dal的“数据库操作”的抽象类，所有的持久化方法均已经基本实现。<BR>
 * TODO:DbDal,已知问题5条。<BR>
 * 1. 所有针对多条记录的操作未认真处理，可能返回的结果不易被调用层理解；<BR>
 * 2. 所有针对多条记录的操作效率不高；<BR>
 * 3. 分页语句只针对MySqL，未按不同数据库的方言进行处理；<BR>
 * 4. 日志没有细致的分类与写入；Fix:9.26修正<BR>
 * 5. 数据库连接池没有考虑；Fix:9.26修正<br>
 * 
 * @param <T>
 */
public abstract class DbDal<T extends Domain> extends Dal<T> implements IDomainSelect<T>, IDomainUpdate<T>
{
	protected IDbHelper _dbHelper = null;
	protected IDbCreator _dbCreator = null;
	protected DbSetting $Setting = null;
	protected IDbVerify _dbVerify = null;
	protected ISqls $Sqls = null;
	protected Fields _fields = null;

	public DbDal(final IDbHelper helper, final Fields fields)
	{
		if ((null == helper) || (null == fields))
		{
			if (null == helper)
				this.logError("实体DAL初始化时 DbHelper == null。Class:" + this.getClass().getSimpleName() + ",对应表:" + this.getTableName());
			if (null == fields)
				this.logError("实体DAL初始化时 Fields == null。Class:" + this.getClass().getSimpleName() + ",对应表:" + this.getTableName());
		}
		this._dbHelper = helper;
		this.$Setting = helper.getDbSetting();
		this.$Sqls = helper.getDbSetting().getSqls();
		this._dbCreator = helper.getDbSetting().getDbCreator();
		if (_dbCreator instanceof OracleDbCreator)
			for (Entry<String, Field> key : fields)
			{
				DataType dType = key.getValue().getDataType();
				if (dType.equals(DataType.VARCHAR) || dType.equals(DataType.TEXT))
					key.getValue().setDataType(DataType.VARCHAR2);
				if (dType.equals(DataType.TINYINT))
					key.getValue().setDataType(DataType.CHAR);
				if (dType.equals(DataType.INT))
					key.getValue().setDataType(DataType.INTEGER);
				if (dType.equals(DataType.DATETIME) || dType.equals(DataType.TIMESTAMP))
					key.getValue().setDataType(DataType.DATE);
			}
		this._dbVerify = helper.getDbSetting().getDbVerify();
		this._fields = fields;

		if (!this.getClass().getSimpleName().equals("CacheDal"))// CacheDal的表检测由该类型自己维护
			this.checkTabel(this.needCheckTable(), this.getTableName());
	}

	/**
	 * 是否需要检查数据表
	 * 
	 * @return
	 */
	protected boolean needCheckTable()
	{
		return true;
	}

	/**
	 * 获取对应的数据表的表名
	 * 
	 * @return
	 */
	public String getTableName()
	{
		return this._fields.getName();
	}

	/**
	 * 检测是否有此张数据表
	 * 
	 * @param fields
	 */
	protected void checkTabel(final boolean needCheckTable, final String tableName)
	{
		if (!needCheckTable)
		{
			this.logInfo("是否检测Cache表的开关为关闭状态。");
			return;
		}
		try
		{
			this.logDebug(_dbHelper.getDbSetting().getDbType() + ", 开始检测数据表:" + tableName + ",表中字段:" + this._fields.keySet());
			String existsSql = _dbCreator.checkTabel(tableName);

			boolean isExist = this.execExistsSql(existsSql);
			if (!isExist)
			{
				// 创建对应的数据表
				String creatSql = _dbCreator.createTabel(_dbHelper.getDbSetting().gettablespace(), tableName, _fields, $Setting);
				this.logDebug(tableName + ",不存在，创建表" + tableName + ",SQL:" + creatSql);
				this._dbHelper.update(creatSql);

				if ($Setting.getDbType() == DB.ORACLE)
				{
					OracleDbCreator ocreator = (OracleDbCreator) _dbCreator;
					String csquString = ocreator.createsequence(tableName);
					this._dbHelper.update(csquString);
					String ctriString = ocreator.createtrigger(tableName);
					this._dbHelper.update(ctriString);
				}
			}

			// oracle 监测字段是出错，待处理。Jert
			// 同类问题出现在this.selectList()中，暂把dbhelper.getResultSet(String cmdText)中执行移到本方法体内问题解决。
			if ($Setting.getDbType() == DB.MYSQL)
				if (this.checkTableFields(tableName))
					this.logDebug("checkTabel:检测数据表:" + tableName + "字段成功.");
				else
					this.logInfo(String.format("checkTabel:检测数据表:%s失败，%s,Drvier:%s。", tableName, $Setting.getDbType(), $Setting.getDatabaseDriver()));
		}
		catch (Exception e)
		{
			e.printStackTrace();
			this.logError(String.format("检测数据库%s失败,Drvier:%s。异常：", tableName, $Setting.getDatabaseDriver(), e.getMessage()));
		}
	}

	/**
	 * 调用SQL语句判断指定的数据库元素是否存在,返回True表示表存在，反之。
	 */
	protected boolean execExistsSql(final String sql)
	{
		int i = _dbHelper.update(sql);
		if (i != 0)
			return true;
		return false;
	}

	/**
	 * 为数据表创建索引
	 * 
	 * @param tablename
	 * @param column
	 */
	protected void createIndexName(final String tablename, final String column)
	{
		try
		{
			String sql = _dbCreator.createIndex(tablename, column);
			this._dbHelper.update(sql);
		}
		catch (Exception e)
		{
			this.logError("数据表字段" + column + "创建索引失败");
			e.printStackTrace();
		}
	}

	/**
	 * 检测是否此张数据表的字段与应用匹配
	 * 
	 * @param fields
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	protected boolean checkTableFields(final String tableName)
	{
		ArrayList<String> fieldList = Lists.newArrayList();
		String selectFieldSql = _dbCreator.checkFields(tableName);
		this.logDebug("查看数据表字段是否存在：" + selectFieldSql);
		ResultSetHandler rsh = new MapListHandler();
		try
		{
			ArrayList rslist = this._dbHelper.query(selectFieldSql, rsh);
			for (int i = 0; i < rslist.size(); i++)
			{
				Map map = (Map) rslist.get(i);
				fieldList.add(map.values().toArray()[0].toString().toLowerCase());
			}
		}
		catch (Exception e)
		{
			this.logError("检测数据库中表中字段失败，表名:" + tableName + "。异常:" + e);
			return false;
		}
		ArrayList<String> indexList = Lists.newArrayList();
		String selectIndexSql = _dbCreator.checkIndex(tableName);
		this.logDebug("查看数据表有多少索引：" + selectIndexSql);
		ResultSetHandler rsh2 = new MapListHandler();
		try
		{
			ArrayList rslist = this._dbHelper.query(selectIndexSql, rsh2);
			for (int i = 0; i < rslist.size(); i++)
			{
				Map map2 = (Map) rslist.get(i);
				indexList.add(map2.get("Column_name").toString().toLowerCase());
			}
		}
		catch (Exception e)
		{
			this.logError("查看数据表有多少索引失败，表名:" + tableName + "。异常:" + e);
			return false;
		}
		try
		{
			this.logDebug(tableName + "表中有索引:" + JSONArray.fromObject(indexList));
			for (Entry<String, Field> entry : this._fields)
			{
				String key = entry.getKey().toLowerCase();
				if (!fieldList.contains(key))
				{
					@SuppressWarnings("deprecation")
					String sqls = $Sqls.toAddTableFieldText(tableName, entry.getValue());
					this.logDebug("添加数据表字段：" + sqls);
					this._dbHelper.update(sqls);
				}
				if ("1".equals(this._fields.get(key).getIndex()) && !indexList.contains(key))
				{
					this.createIndexName(tableName, key);
					this.logDebug("数据库:" + tableName + " 中字段 " + key + " 添加索引成功");
				}
			}
			return true;
		}
		catch (Exception e)
		{
			this.logError("校验数据表索引或添加索引时失败，表名:" + tableName + "。异常:" + e);
			return false;
		}
	}

	// =============

	protected boolean execSql(final String sql)
	{
		try
		{
			int i = this._dbHelper.update(sql);
			if (i == 1)
				return true;
			if (i == -1)
				this.logInfo("SQL执行错误。" + sql);
			if (i == -2)
				this.logError("数据库连接错误。" + sql);
			return false;
		}
		catch (Exception e)
		{
			this.logDebug("数据库驱动程序无法创建一个连接。" + sql, e);
			return false;
		}
	}

	protected boolean execUpdate(final String sql)
	{
		try
		{
			int i = this._dbHelper.update(sql);
			if (i == 1)
				return true;
			if (i == -1)
				this.logError("SQL执行错误。" + sql);
			if (i == -2)
				this.logError("数据库连接错误。" + sql);
			return false;
		}
		catch (Exception e)
		{
			this.logDebug("数据库执行异常。" + sql + e.getMessage());
			return false;
		}
	}

	// =============

	protected T getDomain(final ResultSet rs) throws SQLException
	{
		T t = this.newBlankDomain();
		for (String key : this._fields.keySet())
		{
			Object obj = null;
			try
			{
				obj = rs.getObject(key);
				if (obj != null)
					t.put(key, obj);
			}
			catch (SQLException e)
			{
			}
		}
		return t;
	}

	@SuppressWarnings("rawtypes")
	protected T getDomain(final Map map) throws SQLException
	{
		T t = this.newBlankDomain();
		for (String key : this._fields.keySet())
		{
			Object obj = null;
			try
			{
				obj = map.get(key);
				if (obj != null)
					t.put(key, obj);
			}
			catch (Exception e)
			{
			}
		}
		return t;
	}

	// =============

	@SuppressWarnings({ "unchecked", "rawtypes" })
	protected DomainList<T> selectList(final String sql)
	{
		DomainList<T> list = new DomainList<T>();
		ResultSetHandler rsh = new MapListHandler();
		try
		{
			ArrayList rslist = this._dbHelper.query(sql, rsh);
			if ((null == rslist))
			{
				this.logDebug("返回ResultSet为空。SQL：" + sql);
				return list;
			}
			for (int i = 0; i < rslist.size(); i++)
			{
				Map map = (Map) rslist.get(i);
				T t = this.getDomain(map);
				list.add(t);
			}
		}
		catch (Exception e)
		{
			LogHelper.logError(null, "SelectList查询异常。" + sql, e);
		}
		return list;
	}

	// == 实现IDomainCRUD的接口 ===================================

	@Override
	public int create(final DomainList<T> domainList)
	{
		ArrayList<String> sqls = new ArrayList<String>(domainList.size());
		for (T t : domainList)
			sqls.add($Sqls.toCreateText(this.getTableName(), t));
		return this._dbHelper.update((String[]) sqls.toArray());
	}

	@Override
	public boolean create(final T domain)
	{
		String sql = $Sqls.toCreateText(this.getTableName(), domain);
		sql.replace("  ", " ");
		return this.execUpdate(sql);
	}

	@Override
	public int delete(final DomainList<T> domainList)
	{
		for (T t : domainList)
			this.delete(t);
		return domainList.size();
	}

	@Override
	public int delete(final Id modifiorId, final Where where)
	{
		String sql = $Sqls.toDeleteText(this._fields, modifiorId, where);
		int i = this._dbHelper.update(sql);
		if (i >= 1)
			return i;
		if (i == -1)
			this.logError("SQL执行错误。" + sql);
		if (i == -2)
			this.logError("数据库连接错误。" + sql);
		return i;
	}

	@Override
	public int delete(final Id modifiorId, final String key, final Object value)
	{
		Where sqlSub = Where.where(key, Comparison.Equal, value);
		return this.delete(modifiorId, sqlSub);
	}

	@Override
	public boolean delete(final T domain)
	{
		String sql = $Sqls.toDeleteText(this.getTableName(), domain);
		return this.execSql(sql);
	}

	@Override
	public int deleteAll(final Id modifiorId)
	{
		String sql = $Sqls.toDeleteText(this._fields, modifiorId, null);
		if (this.execSql(sql))
			return this.count(null);
		return 0;
	}

	@Override
	public int delete(final Id modifiorId, final Id id)
	{
		return this.delete(modifiorId, Where.where("id", Comparison.Equal, id.toString()));
	}

	public int delete(Id modifiorId, List<Id> dId)
	{
		try
		{
			for (Id id : dId)
				return this.delete(modifiorId, id);
		}
		catch (Exception e)
		{
			this.logWarn("执行删除多条记录时发生异常。" + dId, e);
			return -3;
		}
		return 1;
	}

	@Override
	public boolean exists(final T domain)
	{
		String sql = $Sqls.toExists(this.getTableName(), domain);
		Object object = this._dbHelper.query(sql, new ScalarHandler("selectCount"));
		if (null == object)
			return false;
		int rowCount = 0;
		try
		{
			if (null != object)
				rowCount = Integer.parseInt(object.toString());
		}
		catch (Exception e)
		{
			this.logWarn("数据查询无。" + sql);
		}
		return rowCount > 0;
	}

	@Override
	public boolean remove(final Id modifiorId, final Id id)
	{
		return this.remove(modifiorId, Where.where("id", Comparison.Equal, id.toString()));
	}

	@Override
	public int remove(final DomainList<T> domainList)
	{
		for (int i = 0; i < domainList.size(); i++)
			if (this.remove(domainList.get(i)))
				return i;
		return domainList.size();
	}

	@Override
	public boolean remove(final Id modifiorId, final Where where)
	{
		String sql = $Sqls.toRemoveText(this._fields, modifiorId, where);
		return this.execSql(sql);
	}

	@Override
	public boolean remove(final Id modifiorId, final String key, final Object value)
	{
		String sql = $Sqls.toRemoveText(this.getTableName(), modifiorId);
		return this.execSql(sql);
	}

	/**
	 * 把实体从实际意义上删除，不可恢复在使用
	 */
	@Override
	public boolean remove(final T domain)
	{
		String sql = $Sqls.toRemoveText(this.getTableName(), domain.getId());
		return this.execSql(sql);
	}

	@Override
	public boolean removeAll(final Id modifiorId)
	{
		// TRUNCATE TABLE name
		String sql = String.format("TRUNCATE TABLE %s", this.getTableName());
		boolean bool = this.execSql(sql);
		if (bool)
			this.logInfo(modifiorId.toString() + String.format("删除表%s中所有数据", this.getTableName()) + "，对应语句：" + sql);
		return bool;
	}

	public int count(final Where where)
	{
		String sql = $Sqls.toCountSelectSqlText(this.getTableName(), this._fields, where);
		int count = 0;
		Object rs = this._dbHelper.query(sql, new ScalarHandler(1));
		if (null == rs)
			return -1;
		try
		{
			if (rs != null)
				count = Integer.parseInt(rs.toString());
		}
		catch (Exception e)
		{
			this.logError(e.getMessage() + sql);
		}
		return count;
	}

	@Override
	public DomainList<T> select(final Columns select, final Where where, final Integer start, final Integer limit)
	{
		String sql = $Sqls.toPageSelectSqlText(this.getTableName(), this._fields, select, where, start, limit);
		this.logDebug("分页查询:" + sql.replace("  ", " "));
		return this.selectList(sql);
	}

	@Override
	public DomainList<T> select(final Columns select, final Where where, final Integer top)
	{
		String sql = $Sqls.toTopSelectSqlText(this.getTableName(), this._fields, select, where, top);
		this.logDebug("Top查询:" + sql.replace("  ", " "));
		return this.selectList(sql);
	}

	@Override
	public DomainList<T> select(final Columns select, final Where where)
	{
		String sql = $Sqls.toSelectSqlText(this.getTableName(), this._fields, select, where);
		this.logDebug("查询:" + sql.replace("'T", "T").replace(")'", ")").replace("''", "'"));
		return this.selectList(sql.replace("'T", "T").replace(")'", ")").replace("''", "'"));
	}

	@Override
	public DomainList<T> select(final Columns select, final String key, final Object value)
	{
		return this.select(select, Where.where(key, Comparison.Equal, value));
	}

	@Override
	public T select(final Id id)
	{
		String sql = $Sqls.toSelectSqlText(this.getTableName(), this._fields, this._fields.toSqlSelectFieldList(), Where.where("id", Comparison.Equal, id.toString()));
		Map<String, Object> rs = this._dbHelper.query(sql, new MapHandler());
		T t = null;
		if (null == rs)
			return null;
		try
		{
			if (rs.size() > 0)
				t = this.getDomain(rs);
		}
		catch (SQLException e)
		{
			this.logError(e.getMessage() + sql);
		}
		return t;
	}

	@Override
	public DomainList<T> selectAll()
	{
		return this.select(this._fields.toSqlSelectFieldList(), null);
	}

	// =============

	@Override
	public boolean update(final Id modifiorId, final HashMap<String, Object> valueMap, final Where where)
	{
		T domain = this.newDomain(modifiorId);
		if (valueMap != null)
			for (String key : valueMap.keySet())
				((Domain) domain).put(key, valueMap.get(key));
		String sql = $Sqls.toUpdateText(this.getTableName(), domain, where);
		return this.execSql(sql);
	}

	@Override
	public T update(final Id modifiorId, final Id Id, final HashMap<String, Object> valueMap)
	{
		T domain = this.newDomain(modifiorId, Id);
		for (String key : valueMap.keySet())
			((Domain) domain).put(key, valueMap.get(key));
		return this.update(domain);
	}

	@Override
	public T update(final Id modifiorId, final Id Id, final String key, final Object value)
	{
		T domain = this.newDomain(modifiorId, Id);
		((Domain) domain).put(key, value);
		return this.update(domain);
	}

	@Override
	public T update(final T domain)
	{
		String sql = $Sqls.toUpdateText(this.getTableName(), domain);
		this.logDebug("更新:" + sql.replace("  ", " "));
		int i = this._dbHelper.update(sql);
		if (i == 1)
			return domain;
		if (i == -1)
			this.logError("SQL执行错误。" + sql);
		if (i == -2)
			this.logError("数据库连接错误。" + sql);
		return null;
	}

}
